﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for LopDAL
/// </summary>
public class LopDAL
{
    public static object IsNull(object checkValue, object replaceValue)
    {
        if (checkValue.Equals(System.DBNull.Value) || checkValue == null)
            return replaceValue;
        return checkValue;
    }

    public static List<LopINFO> listAll()
    {
        string sql = "select * from LOP";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<LopINFO>(dr);
    }

    public static LopINFO GetInfo(int malop)
    {
        string sql = "select * from LOP where malop =" + malop + "";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<LopINFO>(dr);
    }

    public static int Insert(LopINFO obj)
    {
        obj.MaLop = GetMa();
        obj.MaLop = obj.MaLop + 1;
        string sql = "INSERT INTO LOP (MaLop,TenLop) VALUES (" + obj.MaLop + ",N'" + obj.TenLop + "')";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Update(LopINFO obj)
    {
        string sql = "Update LOP set TenLop=N'" + obj.TenLop + "' Where malop=" + obj.MaLop + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Delete(int malop)
    {
        string sql = "Delete from LOP where malop =" + malop + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int GetMa()
    {
        string sql = "SELECT ISNULL(MAX(MaLop),0) FROM LOP";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }
    public static DataTable danhsach(int malop)
    {
        string sql = "SELECT LOP.TenLop, HOCVIEN_LOP.*,HOCVIEN.TenHV FROM LOP INNER JOIN ";
           sql += " HOCVIEN_LOP ON LOP.MaLop = HOCVIEN_LOP.MaLop INNER JOIN ";
           sql += " HOCVIEN ON HOCVIEN_LOP.MaHV = HOCVIEN.MaHV WHERE HOCVIEN_LOP.MaLop = " + malop;
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }
    public static DataTable timkiem(int ma)
    {
        string sql = "SELECT LOP.TenLop, HOCVIEN_LOP.*,HOCVIEN.TenHV FROM LOP INNER JOIN ";
        sql += " HOCVIEN_LOP ON LOP.MaLop = HOCVIEN_LOP.MaLop INNER JOIN ";
        sql += " HOCVIEN ON HOCVIEN_LOP.MaHV = HOCVIEN.MaHV WHERE HOCVIEN_LOP.MaLop = " + ma;
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }
}